"""
数据库表创建脚本
"""

import sys
import os

# 将 backend 目录添加到 Python 路径
backend_dir = os.path.dirname(os.path.dirname(os.path.dirname(__file__)))
sys.path.insert(0, backend_dir)

import asyncio
from db.connection import DatabaseConnection


class TableCreator:
    """数据库表创建器"""

    def __init__(self, db_connection: DatabaseConnection):
        self.db_connection = db_connection

    async def create_all_tables(self):
        """创建所有表"""
        pool = await self.db_connection.get_pool()

        async with pool.acquire() as conn:
            async with conn.cursor() as cursor:
                # 创建用户信息表
                await self._create_users_table(cursor)

                # 创建图书信息表
                await self._create_books_table(cursor)

                # 创建图书主类别表
                await self._create_book_main_categories_table(cursor)

                # 创建图书子类别表
                await self._create_book_sub_categories_table(cursor)

                # 创建用户书架表
                await self._create_user_bookshelf_table(cursor)

                # 创建用户图书阅读位置表
                await self._create_user_reading_positions_table(cursor)

                # 创建用户评分表
                await self._create_user_ratings_comments_table(cursor)

                # 创建用户购买表
                await self._create_user_purchases_table(cursor)

                # 创建用户搜索记录表
                await self._create_user_search_history_table(cursor)

        print("所有数据库表创建完成！")

    async def _create_users_table(self, cursor):
        """创建用户信息表"""
        sql = """
        CREATE TABLE IF NOT EXISTS users (
            user_id INTEGER AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(50) NOT NULL UNIQUE,
            nickname VARCHAR(50) NOT NULL DEFAULT '用户',
            avatar_url TEXT,
            age INTEGER CHECK (age >= 0 AND age <= 150),
            gender VARCHAR(1) CHECK (gender IN ('男', '女')),
            password VARCHAR(64) NOT NULL,
            created_at INTEGER NOT NULL,
            updated_at INTEGER NOT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """
        await cursor.execute(sql)
        print("用户信息表 (users) 创建成功")

    async def _create_books_table(self, cursor):
        """创建图书信息表"""
        sql = """
        CREATE TABLE IF NOT EXISTS books (
            book_id INTEGER PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            author TEXT NOT NULL,
            translator TEXT,
            language VARCHAR(20),
            intro TEXT,
            cover_url TEXT,
            good_rating INTEGER NOT NULL DEFAULT 0,
            fair_rating INTEGER NOT NULL DEFAULT 0,
            poor_rating INTEGER NOT NULL DEFAULT 0,
            rating FLOAT NOT NULL DEFAULT 0.0,
            rating_count INTEGER NOT NULL DEFAULT 0,
            total_chapters INTEGER,
            price FLOAT,
            publish_time VARCHAR(30),
            reading_count INTEGER DEFAULT 0
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """
        await cursor.execute(sql)
        print("图书信息表 (books) 创建成功")

    async def _create_book_main_categories_table(self, cursor):
        """创建图书主类别表"""
        sql = """
        CREATE TABLE IF NOT EXISTS book_main_categories (
            book_id INTEGER,
            main_category VARCHAR(10) NOT NULL,
            PRIMARY KEY (book_id, main_category),
            FOREIGN KEY (book_id) REFERENCES books(book_id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """
        await cursor.execute(sql)
        print("图书主类别表 (book_main_categories) 创建成功")

    async def _create_book_sub_categories_table(self, cursor):
        """创建图书子类别表"""
        sql = """
        CREATE TABLE IF NOT EXISTS book_sub_categories (
            book_id INTEGER,
            sub_category VARCHAR(10) NOT NULL,
            PRIMARY KEY (book_id, sub_category),
            FOREIGN KEY (book_id) REFERENCES books(book_id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """
        await cursor.execute(sql)
        print("图书子类别表 (book_sub_categories) 创建成功")

    async def _create_user_bookshelf_table(self, cursor):
        """创建用户书架表"""
        sql = """
        CREATE TABLE IF NOT EXISTS user_bookshelf (
            user_id INTEGER,
            book_id INTEGER,
            add_at INTEGER NOT NULL,
            PRIMARY KEY (user_id, book_id),
            FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
            FOREIGN KEY (book_id) REFERENCES books(book_id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """
        await cursor.execute(sql)
        print("用户书架表 (user_bookshelf) 创建成功")

    async def _create_user_reading_positions_table(self, cursor):
        """创建用户图书阅读位置表"""
        sql = """
        CREATE TABLE IF NOT EXISTS user_reading_positions (
            user_id INTEGER,
            book_id INTEGER,
            position INTEGER NOT NULL,
            created_at INTEGER NOT NULL,
            updated_at INTEGER NOT NULL,
            PRIMARY KEY (user_id, book_id),
            FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
            FOREIGN KEY (book_id) REFERENCES books(book_id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """
        await cursor.execute(sql)
        print("用户图书阅读位置表 (user_reading_positions) 创建成功")

    async def _create_user_ratings_comments_table(self, cursor):
        """创建用户评分评论表"""
        sql = """
        CREATE TABLE IF NOT EXISTS user_ratings_comments (
            user_id INTEGER,
            book_id INTEGER,
            rating FLOAT NOT NULL,
            comment TEXT,
            created_at INTEGER NOT NULL,
            updated_at INTEGER NOT NULL,
            PRIMARY KEY (user_id, book_id),
            FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
            FOREIGN KEY (book_id) REFERENCES books(book_id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """
        await cursor.execute(sql)
        print("用户评分表 (user_ratings_comments) 创建成功")

    async def _create_user_purchases_table(self, cursor):
        """创建用户购买表"""
        sql = """
        CREATE TABLE IF NOT EXISTS user_purchases (
            user_id INTEGER,
            book_id INTEGER,
            price FLOAT NOT NULL,
            created_at INTEGER NOT NULL,
            updated_at INTEGER NOT NULL,
            PRIMARY KEY (user_id, book_id),
            FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
            FOREIGN KEY (book_id) REFERENCES books(book_id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """
        await cursor.execute(sql)
        print("用户购买表 (user_purchases) 创建成功")

    async def _create_user_search_history_table(self, cursor):
        """创建用户搜索记录表"""
        sql = """
        CREATE TABLE IF NOT EXISTS user_search_history (
            user_id INTEGER,
            search_query TEXT NOT NULL,
            created_at INTEGER NOT NULL,
            updated_at INTEGER NOT NULL,
            PRIMARY KEY (user_id, search_query(255)),
            FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """
        await cursor.execute(sql)
        print("用户搜索记录表 (user_search_history) 创建成功")

    async def drop_all_tables(self):
        """删除所有表（谨慎使用）"""
        pool = await self.db_connection.get_pool()

        async with pool.acquire() as conn:
            async with conn.cursor() as cursor:
                # 按照外键依赖顺序删除表
                tables = [
                    "user_search_history",
                    "user_purchases",
                    "user_ratings_comments",
                    "user_reading_positions",
                    "user_bookshelf",
                    "book_sub_categories",
                    "book_main_categories",
                    "books",
                    "users",
                ]

                for table in tables:
                    await cursor.execute(f"DROP TABLE IF EXISTS {table}")
                    print(f"表 {table} 已删除")

        print("所有表已删除")


async def main():
    """主函数 - 创建所有表"""
    from db import db_connection

    creator = TableCreator(db_connection)

    try:
        print("开始创建数据库表...")
        await creator.drop_all_tables()  # 可选：先删除所有表
        await creator.create_all_tables()
        print("数据库表创建完成！")
    except Exception as e:
        print(f"创建表时发生错误: {e}")
    finally:
        await db_connection.close_pool()


if __name__ == "__main__":
    asyncio.run(main())
